import { asyncFunnel } from "async-funnel"
const fs = require("node:fs")
const path = require("node:path")
function loadAddon() {
    let fn = ""
    let ps = [
        "../../../lib/",
        "../../../",
        "../build/Release/",
        "../node_modules/mini-sqlite3/build/Release/",
        "../lib/",
        "../",
        "./lib/",
        "./static/lib/",
        "./",
    ]
    for (let i = 0; i < ps.length; i++) {
        let n = ps[i]
        let tmpfn = path.join(__dirname, n, 'mini_sqlite3_addon.node')
        try {
            if (fs.existsSync(tmpfn)) {
                fn = tmpfn
                break
            }
        } catch (e) {
        }
    }
    if (!fn) {
        let g: any = global
        if (g && g.mini_sqlite3_node_addon_pathname) {
            fn = g.mini_sqlite3_node_addon_pathname
        }
    }
    if (!fn) {
        console.error("can not found mini_sqlite3_addon.node in:", ps)
        console.error("or set global.mini_sqlite3_node_addon_pathname = addon_pathname")
        process.exit(1)
    }

    const addon = require(fn)
    return addon;
}

let addon = loadAddon()

let id_prefix = "id_" + (new Date()).getTime()

export type Sqlite3BindValue = Buffer | string | number | boolean | bigint

export type Sqlite3Options = {
    dbName: string,
}

export class sqlite3Client {
    dbName: string
    db: any
    funnel: asyncFunnel
    constructor() {
        this.dbName = ""
        this.db = new addon.sqlite3_mini_client_forge()
        this.funnel = new asyncFunnel()
    }
    open(options: Sqlite3Options) {
        this.dbName = options.dbName
        if (!addon.sqlite3_mini_client_open(this.db, this.dbName)) {
            return false
        }
        return true
    }
    close() {
        if (!addon.sqlite3_mini_client_close(this.db)) {
            return false
        }
        return true
    }
    async worker(handler: (worker: sqlite3Worker) => Promise<any>) {
        await this.funnel.run(async () => {
            let w = new sqlite3Worker({
                client: this
            })
            await handler(w);
            (w as any).clearAllStmt()
        })
    }
}

export class sqlite3Worker {
    private client: sqlite3Client
    private db: any
    allStmt: { [key: string]: sqlite3Stmt }
    constructor(options: { client: sqlite3Client }) {
        this.client = options.client
        this.db = this.client.db
        this.allStmt = {}
        this.clearAllStmt()
    }
    exec(sql: string) {
        sql += ""
        if (!addon.sqlite3_mini_client_exec(this.db, sql)) {
            return false
        }
        return true
    }
    transactionExec(sql: string | string[]) {
        if (!Array.isArray(sql)) {
            sql = [sql + ""]
        }
        return this.transaction(() => {
            for (let i = 0; i < sql.length; i++) {
                if (!addon.sqlite3_mini_client_exec(this.db, sql[i])) {
                    return false
                }
            }
        })
    }
    begin() {
        if (!addon.sqlite3_mini_client_begin(this.db)) {
            return false
        }
        return true
    }
    begin_EXCLUSIVE() {
        if (!addon.sqlite3_mini_client_begin_EXCLUSIVE(this.db)) {
            return false
        }
        return true
    }
    begin_SHARED() {
        if (!addon.sqlite3_mini_client_begin_SHARED(this.db)) {
            return false
        }
        return true
    }
    commit() {
        if (!addon.sqlite3_mini_client_commit(this.db)) {
            return false
        }
        return true
    }
    rollback() {
        if (!addon.sqlite3_mini_client_rollback(this.db)) {
            return false
        }
        return true
    }
    transaction(job: () => boolean | undefined | void) {
        let ok = false
        this.begin()
        try {
            if (job() !== false) {
                if (this.commit()) {
                    ok = true
                }
            }
        } catch (e) {
            console.log(e)
        }
        if (!ok) {
            this.rollback()
        }
        return ok
    }

    stmt(options?: { sql?: string }) {
        let mt = new sqlite3Stmt({ client: this.client, worker: this })
        options = options || {}
        if (options.sql) {
            mt.prepare(options.sql)
        }
        return mt
    }

    private clearAllStmt() {
        let keys = Object.keys(this.allStmt)
        for (let i = 0; i < keys.length; i++) {
            this.allStmt[keys[i]].close();
        }
    }
}

export class sqlite3Stmt {
    private sql: string
    private uid: string
    private client: sqlite3Client
    private stmt: any
    private worker: sqlite3Worker
    private parameterCount: number
    private parameterNames: any
    private columnInfosGetted: boolean
    private columnInfos: any
    constructor(options: { client: sqlite3Client, worker: sqlite3Worker }) {
        this.sql = ""
        this.uid = id_prefix + "_" + (new Date()).getTime()
        this.client = options.client
        this.stmt = new addon.sqlite3_mini_stmt_forge()
        this.worker = options.worker
        this.parameterCount = -1
        this.parameterNames = {}
        this.columnInfosGetted = false
        this.columnInfos = []
        addon.sqlite3_mini_stmt_new(this.client.db, this.stmt)
    }
    close() {
        let r = addon.sqlite3_mini_stmt_close(this.stmt) ? true : false
        if (this.uid in this.worker.allStmt) {
            delete this.worker.allStmt[this.uid]
        }
        return r
    }
    prepare(sql: string) {
        this.worker.allStmt[this.uid] = this
        this.parameterCount = -1
        this.parameterNames = {}
        this.columnInfosGetted = false
        this.columnInfos = []
        let r: string = addon.sqlite3_mini_stmt_prepare(this.stmt, sql)
        if (r == "") {
            return false
        }
        let rs = r.split(";")
        this.parameterCount = parseInt(rs[0])
        let ps: any = {}
        rs.slice(1).forEach((name, idx) => {
            ps[name] = idx + 1
        })
        this.parameterNames = ps
        this.sql = sql
        return true
    }

    private bind_with_position(sn: number, val: Buffer | string | number | boolean | bigint): boolean {
        if (this.parameterCount < 1) {
            console.warn("sqlite3: no bind parameter, sn:", sn, ", val:", val)
            return false
        }
        if (sn < 1) {
            console.warn("sqlite3: sn must > 0, sn:", sn, ", val:", val)
            return false
        }
        if (sn > this.parameterCount) {
            console.warn("sqlite3: sn must < ", this.parameterCount + 1, ", sn:", sn, ", val:", val)
            return false
        }
        let r = false
        let type = typeof val
        if (Buffer.isBuffer(val)) {
            r = addon.sqlite3_mini_stmt_bind_blob(this.stmt, sn, val)
        } else if (type == "string") {
            r = addon.sqlite3_mini_stmt_bind_text(this.stmt, sn, val)
        } else if (type == "bigint") {
            r = addon.sqlite3_mini_stmt_bind_long(this.stmt, sn, val)
        } else if (type == "number") {
            r = addon.sqlite3_mini_stmt_bind_long(this.stmt, sn, val)
        } else if (type == "boolean") {
            r = addon.sqlite3_mini_stmt_bind_long(this.stmt, sn, val ? 1 : 0)
        } else {
        }
        if (!r) {
            console.warn("sqlite3: bind error, sn:", sn, ", val:", val, ", sql:", this.sql)
        }
        return r
    }
    bind(bindInfos: (Buffer | string | number | boolean | bigint)[] | { [key: string]: Buffer | string | number | boolean | bigint }): boolean {
        if (Array.isArray(bindInfos)) {
            bindInfos = [...bindInfos]
            for (let i = 0; i < bindInfos.length; i++) {
                let sn = i + 1
                let val = bindInfos[i]
                let r = this.bind_with_position(sn, val)
                if (!r) {
                    return false
                }
            }
        } else {
            bindInfos = { ...bindInfos }
            let keys = Object.keys(bindInfos)
            let ccc = 0
            for (let i = 0; i < keys.length; i++) {
                let key = keys[i]
                let sn = this.parameterNames[key]
                if (!sn) {
                    continue
                }
                ccc++
                let r = this.bind_with_position(sn, bindInfos[key])
                if (!r) {
                    return false
                }
            }
            if (ccc != this.parameterCount) {
                console.warn("sqlite3: bind error, parameter missing")
                return false
            }
        }
        return true
    }

    private inner_step(options: { needResult: boolean, needTableName?: boolean }): { status: boolean, row?: { [key: string]: any } } {
        options = { ...options }
        let that = this
        let row: any = {}
        let r = addon.sqlite3_mini_stmt_step(this.stmt)
        if (r == 0) {
            return { status: true }
        } else if (r != 1) {
            this.close()
            return { status: false }
        } else {
        }
        if (options.needResult) {
            if (!this.columnInfosGetted) {
                if (!this.prepare_column_infos()) {
                    return { status: false }
                }
            }
            let ps: any[] = this.columnInfos
            for (let sn = 0; sn < ps.length; sn++) {
                let info = ps[sn]
                let val: any
                if (info.type == "number") {
                    val = addon.sqlite3_mini_stmt_column_long(this.stmt, sn)
                } else if (info.type == "float") {
                    val = addon.sqlite3_mini_stmt_column_float(this.stmt, sn)
                } else if (info.type == "text") {
                    val = addon.sqlite3_mini_stmt_column_text(that.stmt, sn)
                } else /* if (info.type == "blob") */ {
                    val = addon.sqlite3_mini_stmt_column_blob(this.stmt, sn)
                }
                if (val === false) {
                    return { status: false }
                }
                row[info.name] = val
                if (options.needTableName) {
                    row[info.tname] = val
                }
            }
        }
        return { status: true, row }
    }

    run(): boolean {
        let r = this.inner_step({ needResult: false })
        return r.status
    }

    step(): { status: boolean, row?: { [key: string]: any } } {
        let r = this.inner_step({ needResult: true })
        return r
    }

    stepByStep(handler: (row: { [key: string]: any }) => any, options?: { needTableName?: boolean }): boolean {
        options = options || {}
        while (1) {
            let r = this.inner_step({ needResult: true, needTableName: options.needTableName })
            if (!r.status) {
                return false
            }
            let row = r.row
            if (!row) {
                break
            }
            handler({ ...row })
        }
        return true
    }

    runAndGetAllRows(): { status: boolean, rows: { [key: string]: any }[] } {
        let result: { status: boolean, rows: { [key: string]: any }[] } = {
            status: false,
            rows: [],
        }
        while (1) {
            let r = this.inner_step({ needResult: true, needTableName: true })
            if (!r.status) {
                return result
            }
            let row = r.row
            if (!row) {
                result.status = true
                break
            }
            result.rows.push(row)
        }
        return result
    }

    private prepare_column_infos() {
        if (this.columnInfosGetted) {
            return true
        }
        let r = addon.sqlite3_mini_stmt_get_column_info(this.stmt)
        if (r == "") {
            return false
        }
        let ps: any[] = []
        let rs = r.split(";")
        let count = parseInt(rs[0])
        for (let i = 1; i <= count; i++) {
            let ss = rs[i].split(",")
            ps.push({ name: ss[0], tname: ss[1], type: ss[2] })
        }
        this.columnInfos = ps
        this.columnInfosGetted = true
        return true
    }
}